# -*- coding: utf-8 -*-
"""
Created on Wed Jun 17 16:17:38 2020

@author: my
"""


import requests
import json, re
#import MySQLdb
import mysql.connector


url = 'https://h5api.m.taobao.com/h5/mtop.alimama.union.sem.landing.pc.items/1.0/?jsv=2.4.0&appKey=12574478&t=1592380690017&sign=368d4db4b977bab71716bb660e261167&api=mtop.alimama.union.sem.landing.pc.items&v=1.0&AntiCreep=true&dataType=jsonp&type=jsonp&ecode=0&callback=mtopjsonp1&data=%7B%22keyword%22%3A%22%E7%BA%A2%E5%AF%8C%E5%A3%AB%E8%8B%B9%E6%9E%9C%22%2C%22ppath%22%3A%22%22%2C%22loc%22%3A%22%22%2C%22minPrice%22%3A%22%22%2C%22maxPrice%22%3A%22%22%2C%22ismall%22%3A%22%22%2C%22ship%22%3A%22%22%2C%22itemAssurance%22%3A%22%22%2C%22exchange7%22%3A%22%22%2C%22custAssurance%22%3A%22%22%2C%22b%22%3A%22%22%2C%22clk1%22%3A%22df6530d16b3208828b55de25e9a6d643%22%2C%22pvoff%22%3A%22%22%2C%22pageSize%22%3A%22100%22%2C%22page%22%3A%220%22%2C%22elemtid%22%3A%221%22%2C%22refpid%22%3A%22mm_26632258_3504122_32538762%22%2C%22pid%22%3A%22430673_1006%22%2C%22featureNames%22%3A%22spGoldMedal%2CdsrDescribe%2CdsrDescribeGap%2CdsrService%2CdsrServiceGap%2CdsrDeliver%2C%20dsrDeliverGap%22%2C%22ac%22%3A%22zV0DFxG9PRUCASSAgdgexrXz%22%2C%22wangwangid%22%3A%22t_1514168928654_0773%22%2C%22catId%22%3A%22%22%7D'
cookie="XSRF-TOKEN=c4493474-0afd-479d-9a58-d435314cac90;_m_h5_tk=220e8f757b61862e81dd3ce601a9db27_1592388012955;_m_h5_tk_enc=2c0e745962231609d8b8dc195968ce8d"
headers = {
    'Accept': 'text/html, application/xhtml+xml, image/jxr, */*',
               'Accept - Encoding':'gzip, deflate',
               'Accept-Language':'zh-Hans-CN, zh-Hans; q=0.5',
               'Connection':'Keep-Alive',
    'Referer': 'https://uland.taobao.com/sem/tbsearch?refpid=mm_26632258_3504122_32538762&clk1=df6530d16b3208828b55de25e9a6d643&keyword=%E7%BA%A2%E5%AF%8C%E5%A3%AB%E8%8B%B9%E6%9E%9C&page=0',
    'User-Agent':'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.90 Safari/537.36',
    'Cookie': cookie
    
    }

response = requests.get(url, headers=headers )
response.encoding = 'utf-8'

print(response.text)

data = json.loads(re.match(".*?({.*}).*", response.text, re.S).group(1))

print(data)



# 打开数据库连接
#db = MySQLdb.connect("localhost", "root", "123456", "lexian", port=3306,charset='utf8' )
 
# 连接database
db = mysql.connector.connect(
   host='localhost',
   user='root',
   password='123456',
   database='lexian',
   charset='utf8')


# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# 使用execute方法执行SQL语句
sql = """INSERT INTO  taobao (title, loc, price, DATA) VALUES(%s, %s, %s, CURRENT_DATE ) """ 
print( data['data']['mainItems'])
try:
    
    for item in data['data']['mainItems']:
            
        #INSERT INTO  taobao (title, loc, price, DATA) VALUES(%s, %s, %f, CURRENT_DATE )
        cursor.execute(sql, (item['title'], item['loc'], str(float(item['price']))))

    db.commit()
except Exception as e:
    db.rollback()
    print(e)
    

# 关闭数据库连接
db.close()

    



